<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2015/9/28
 * Time: 10:31
 */
namespace Home\Controller;
use Think\Controller;

class PHPExcelController extends Controller{

    public function __construct(){
        parent::__construct();
        vendor('PHPExcel');
    }

    /**
     * 第三方类库，Excel、导入导出
     */
    public function index(){
    }

    /**
     * excel 导出 入口
     */
   public function excel_out($name=null,$model=null,$map=array()){
        $is_pay  = I('get.is_pay') ? I('get.is_pay') : '';
        $name = $name ?$name : '学生信息';
        $model  = $model? $model : D('CountVisit');
        $xlsCell  = array(
            array('id','编号'),
            array('ip','客户ip地址'),
            array('url','访问的地址'),
            array('vtime','访问时间'),
        );

        $xlsData  = $model->field('id,ip,url,vtime')->where($map)->select();
//        dump($xlsData[0]);exit;

        //数据处理
        foreach($xlsData as $k=>$v){
            $xlsData[$k]['ip'] = long2ip($v['ip']);

            $xlsData[$k]['vtime'] = date('Y-m-d H:i:s',$v['vtime']);
        }
        $this->exportExcel($name,$xlsCell,$xlsData);

    }

    /**
     * 导出 数据
     * @param string $expTitle : 导出的 excel文件名称
     * @param array  $expCellName : 行名称
     * @param $expTableData
     */
    public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName); //列数
        $dataNum = count($expTableData);

        $objPHPExcel = new \PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        //设置 --表格 宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(120);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * excel 批量 导入 - 入口
     */
    public function excel_implode(){
//        header('Content-type:text/html;charset=utf-8');
        $class_id = I('post.class_id');

        $file = $_FILES;
        if($file['excel']['name'] != ''){
            //上传 excel 表
            $file_info = A('Admin/ImgUp')->upExcel($file['excel']);
        }else{
            $this->error('请选择上传文件!');
        }

        $file_name = './Uploads'.substr($file_info['savepath'],1).$file_info['savename'];
        //读取文件 信息
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel = $objReader->load($file_name,$encode='utf-8');

        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数

        //循环 获取 数据信息
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        for($i=3;$i<=$highestRow;$i++) {
//            dump($objPHPExcel->getActiveSheet()->getCell('A1')->getValue());exit;
            $data[] = array(
                'chinese_name'      => $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getValue(),
                'id_number' => $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getValue(),
//                'class_number'          => $objPHPExcel->getActiveSheet()->getCell('D'.$i)->getValue(),
                'grades'          => $objPHPExcel->getActiveSheet()->getCell('D'.$i)->getValue(),
            );
        }

        $this->get_stu_id_and_insert($data,$class_id);

        $this->success('导入成功!');

    }

    /**
     * 根据 学生 信息 得到学生的id，然后 导入学生成绩
     * @param array $data : 从excel 表中读取的信息
     */
    public function get_stu_id_and_insert($data,$class_id){
        //获取 学生信息
        $stu_info  = $this->get_stu_id();

        foreach($data as $k=>$v){
            $map['class_id'] = array('eq',$class_id);
            $map['stu_id'] = array('eq',$stu_info[$v['id_number']]);
            //跟新 用户信息
            D('Home/ClassStu')->where($map)->setField('grades',$v['grades']);
        }
        return true;
    }

    /**
     * 获取 数据库中 选择 所有学生信息，对应 '身份证号' => id
     */
    public function get_stu_id(){
        $stu_model  = D('Home/Stuserinfo');

        $stu_data = $stu_model->getAlls('','id,id_number');
        foreach($stu_data as $k=>$v){
            $data[$v['id_number']] = $v['id'] ;
        }
        return $data;
    }
}